/*
#大写
SHOW DATABASES ;#查看数据库

CREATE DATABASE mydb;   #创建数据库
CREATE DATABASE mydb1 CHARSET = utf8;
CREATE DATABASE mydb2 CHARSET = GBK;

SHOW CREATE DATABASE mydb ; #查看数据库创建语句
SHOW CREATE DATABASE mydb1 ;
SHOW CREATE DATABASE mydb2 ;


DROP DATABASE mydb; #删除数据库
DROP DATABASE mydb1;
DROP DATABASE mydb2;

USE mydb;   #选择数据库
USE mydb1;
USE mydb2;

CREATE TABLE user(  #创建表
                     id INT,                 #id字段，类型为整型
                     username VARCHAR(32),   #username字段，类型为字符串，长度32（可以存32个字符）
                     password VARCHAR(32),
                     nickname VARCHAR(32),
                     age INT(3)              #age字段，类型为整型，长度3，最多存3为整数
);

DESC user;  #查看表结构
DESC userinfo;

SHOW CREATE TABLE user; #查看表创建的信息

SHOW TABLES ;   #查看所有表

RENAME TABLE user TO userinfo;  #修改表名

DROP TABLE userinfo;    #删除表

CREATE TABLE hero(
    name VARCHAR(32),
    AGE INT
);

ALTER TABLE hero ADD gender VARCHAR(10);    #修改表结构
DESC hero;
ALTER TABLE hero ADD id INT FIRST;  #在表的最开始添加字段
ALTER TABLE hero ADD pwd VARCHAR(32) AFTER name;    #在指定位置添加字段
ALTER TABLE hero DROP pwd;  #删除字段
ALTER TABLE hero CHANGE age age INT(5); #修改表字段
ALTER TABLE hero CHANGE age age VARCHAR(20);
ALTER TABLE hero CHANGE gender nickname VARCHAR(30) NOT NULL;

CREATE DATABASE mydb4 CHARSET = utf8;
SHOW DATABASES;
USE mydb4;
CREATE TABLE teacher(
    name VARCHAR(32)
);
DESC teacher;
DESC t;
SHOW TABLES;
ALTER TABLE teacher ADD age INT(3);
ALTER TABLE teacher ADD id INT FIRST;
ALTER TABLE teacher ADD salary INT AFTER name;
ALTER TABLE teacher DROP age;
RENAME TABLE teacher TO t;
DROP TABLE t;
DROP DATABASE mydb4;

USE mydb;
CREATE TABLE person(
    name VARCHAR(30),
    age INT(3)
);
SHOW TABLES;
DESC hero;
DESC person;
INSERT INTO person (name,age) VALUES('张三',33);  #插入数据
INSERT INTO person (age,name) VALUES (18,'李四');
INSERT INTO person (name,age) VALUES (20,'王五'); #错误
INSERT INTO person (age,name) VALUES (18);       #错误
INSERT INTO person (name) VALUES ('王五');
SELECT * FROM person;   #*表示所有列,查看表中所有记录

CREATE TABLE myperson(
    name VARCHAR(30) DEFAULT '无名氏',
    age INT DEFAULT 18
);
DESC myperson;
SELECT * FROM myperson;
DESC person;
ALTER TABLE person CHANGE name name VARCHAR(30) DEFAULT '无名氏';
INSERT INTO myperson (name) VALUES ('张三');
INSERT INTO myperson (age) VALUES (30);
INSERT INTO myperson VALUES ('李四',50);
INSERT INTO myperson VALUES ('李四');     #错误
INSERT INTO myperson VALUES (50,'李四');  #错误
INSERT INTO myperson VALUES (DEFAULT,99);
INSERT INTO myperson VALUES (NULL,DEFAULT);
INSERT INTO myperson(name, age) VALUES ('老大',1),('老二',2),('老三',3);

UPDATE person
SET age = 40;
SELECT * FROM person;

UPDATE person
SET age = 22
WHERE name='张三';

UPDATE person
SET age=36
WHERE age=40;

UPDATE person
SET age=39
WHERE age>30;
SELECT * FROM person;

UPDATE person
SET name='李老四',age=66
WHERE name='李四';
SHOW TABLES;

DELETE FROM person
WHERE name='李老四';

DELETE FROM person
WHERE age<30;
SELECT * FROM person;
INSERT INTO person VALUES (DEFAULT,99);
INSERT INTO person VALUES (NULL,DEFAULT);
INSERT INTO person(name, age) VALUES ('老大',1),('老二',2),('老三',3);

SHOW DATABASES ;
CREATE DATABASE day2db CHARSET = utf8;
SHOW CREATE DATABASE day2db;
USE day2db;
CREATE TABLE t_hero(
    name VARCHAR(30)
);
DESC t_hero;
RENAME TABLE t_hero TO hero;
DESC hero;
ALTER TABLE hero ADD money INT(6);
ALTER TABLE hero ADD id INT FIRST;
ALTER TABLE hero ADD age INT(3) AFTER name;
INSERT INTO hero(id,name,age,money)
VALUES (1,'李白',50,6888),(2,'赵云',30,13888),(3,'刘备',25,6888);
SELECT *FROM hero;
UPDATE hero
SET age=52
WHERE name='刘备';

UPDATE hero
SET money=5000
WHERE age<=50;

DELETE FROM hero
WHERE money=5000;

DROP TABLE hero;
SHOW TABLES;
DROP DATABASE day2db;
SHOW DATABASES;
SHOW TABLES;    #失败
CREATE TABLE hero(
    id INT
);  #失败，No database selected
USE mydb;
CREATE TABLE person1(
    id INT,
    age INT UNSIGNED
);
SHOW TABLES;
DESC person1;
INSERT INTO person1(id) VALUES (1.1);
INSERT INTO person1(id) VALUES (4.4);
INSERT INTO person1(id) VALUES (4.6);#四舍五入
INSERT INTO person1(age) VALUES (-1);#失败
SELECT * FROM person1;
SHOW TABLES;

CREATE TABLE person2(
    age INT(3), #最大999
    salary DOUBLE(7,2)  #最大99999.99
);
DESC person2;
SELECT * FROM person2;
DELETE FROM person2
WHERE salary=9999.99;
INSERT INTO person2(salary) VALUES (99999.99);
INSERT INTO person2(salary) VALUES (999999);    #错误
INSERT INTO person2(salary) VALUES (1234.567);  #1234.57四舍五入
INSERT INTO person2(salary) VALUES (99999.994);
INSERT INTO person2(salary) VALUES (99999.996); #错误
SELECT * FROM person2;
SHOW TABLES;
CREATE TABLE userinfo(
    id INT,
    name VARCHAR(30),
    gender CHAR(1),
    birth DATETIME,
    salary DOUBLE(7,2)
);
DESC userinfo;
SELECT * FROM userinfo;
INSERT INTO userinfo (id,name,gender,birth,salary)
VALUES (1,'张三','男','1992-12-24 23:12:50',123.456);
DELETE FROM userinfo
WHERE id=1;
INSERT INTO userinfo (id,name,gender,birth,salary)
VALUES (2,'李四','女','1992-12-24',2500.25);
INSERT INTO userinfo (id,name,gender,birth,salary)
VALUES (1,'张三','男','23:12:50',123.456); #错误
SHOW TABLES;
CREATE TABLE user1(
    id INT PRIMARY KEY ,
    name VARCHAR(30),
    age INT(3)
);
DESC user1;
SELECT * FROM user1;
INSERT INTO user1(id,name,age)VALUES (1,'张三',22),(2,'李四',33);
INSERT INTO user1(id,name,age)VALUES (1,'张三',22);#错误，重复主键
INSERT INTO user1(id,name,age)VALUES (NULL,'李四',33);#错误，主键不能为空
INSERT INTO user1(name,age)VALUES ('李四',33);#错误，主键不能为空

UPDATE user1
SET id=1
WHERE name='李四';#错误，id=1已经有了

UPDATE user1
SET id=NULL
WHERE name='张三';#错误，主键不能为空

CREATE TABLE user2(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    age INT(3)
);
DESC user2;
DESC user1;
SELECT * FROM user2;
ALTER TABLE user1 CHANGE id id INT AUTO_INCREMENT;
INSERT INTO user2 (name, age) VALUES ('张三',22);
INSERT INTO user2 (name, age) VALUES ('李四',34);
INSERT INTO user2 (id,name, age) VALUES (NULL,'王五',44);

CREATE TABLE user3(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    age INT(3) NOT NULL
);
DESC user3;
DESC user2;
ALTER TABLE user2 CHANGE name name VARCHAR(30) NOT NULL ;
INSERT INTO user3 (name, age) VALUES (NULL,22);#错误
INSERT INTO user3 (age) VALUES (22);#错误
SELECT * FROM user3;
INSERT INTO user3 (name, age) VALUES ('张三',22);
INSERT INTO user3 (name, age) VALUES ('张三',33);

UPDATE user3
SET name=NULL
WHERE name='张三';#错误

CREATE TABLE user4(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) UNIQUE,
    age INT(3)
);
DESC user4;
SELECT * FROM user4;

ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE;#，不能有重复值，默认可以为空
ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE NOT NULL ;
DESC user3;
SELECT * FROM user3;
UPDATE user3
SET name='李四'
WHERE id=2;

DESC user4;
SELECT * FROM user4;
INSERT INTO user4 (name, age) VALUES ('张三',22);
INSERT INTO user4 (name, age) VALUES ('李四',33);
INSERT INTO user4 (name, age) VALUES ('李四',55);#错误
INSERT INTO user4 (name, age) VALUES (NULL,55);
INSERT INTO user4 (name, age) VALUES (NULL,66);
INSERT INTO user4 (id,name, age) VALUES (6,NULL,66);
DELETE FROM user4
WHERE id=6;

CREATE TABLE user5(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30),
    age  INT(3) CHECK ( age > 0 AND age < 120 )
);
DESC user5;
SELECT * FROM user5;
INSERT INTO user5 (name, age) VALUES ('张三',20);
INSERT INTO user5 (name, age) VALUES ('李四',0);
INSERT INTO user5 (name, age) VALUES ('李四',150);

UPDATE user5
SET age=150
WHERE id=1;


USE tedu;
SELECT * FROM teacher;
SELECT name,salary,gender,title
FROM teacher;

SELECT name,age,gender,birth
FROM student;

SELECT name,age,title,salary
FROM teacher
WHERE title='一级讲师';

SELECT name,salary,comm,title
FROM teacher
WHERE name<>'刘苍松';

SELECT name,title,salary,comm,age
FROM teacher
WHERE age>=30;

SELECT name,age,gender
FRom student
WHERE job='大队长';

SELECT name,floor
FROM class
WHERE floor>=2;

SELECT name,age,gender,job
FROM student
WHERE age=7 AND job='大队长';

SELECT name,title,salary
FROM teacher
WHERE title='一级讲师' OR title='三级讲师';

SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<6 AND job='中队长';

SELECT name,gender,age,job
FROM student
WHERE job='大队长' OR job='中队长' OR job='小队长';

#AND优先级高于OR，因此可以用（）
SELECT name,age,gender,job,class_id
FROM student
WHERE class_id<=6 AND (job='大队长' OR job='中队长');

SELECT name,gender,age,job
FROM student
#WHERE job='大队长' OR job='中队长' OR job='小队长';
WHERE job IN('大队长','中队长','小队长');#这两句等价

SELECT name,title,salary,gender
FROM teacher
WHERE title IN('一级讲师','二级讲师','三级讲师');

SELECT name,title,salary
FROM teacher
#WHERE title<>'一级讲师' AND title<>'二级讲师';
WHERE title NOT IN('一级讲师','二级讲师');#这两句等价

SELECT name,gender,age,salary
FROM teacher
#WHERE salary>=2000 AND salary<=5000;
WHERE salary BETWEEN 2000 AND 5000;#两句等价

SELECT name,gender,age
FROM student
WHERE age BETWEEN 7 AND 10;

SELECT name,gender,age,title
FROM teacher
WHERE age BETWEEN 20 AND 35 AND gender='男';

SELECT DISTINCT title
FROM teacher;

SELECT DISTINCT job
FROM student;

SELECT DISTINCT age,job
FROM student;

SELECT name,gender,subject_id
FROM teacher
WHERE subject_id=1 AND gender='男';

SELECT name,gender,salary
FROM teacher
WHERE salary>5000 AND gender='女';

SELECT name,gender,salary
FROM teacher
WHERE salary>5000 AND gender='男'
OR gender='女';

SELECT name,age,job
FROM student
WHERE age=9 AND job IN('学习委员','语文课代表');

SELECT name,salary
FROM teacher
WHERE salary BETWEEN 6000 AND 10000;
6.查看工资在4000到8000以外的老师及具体工资?
SELECT name,salary
FROM teacher
WHERE salary NOT BETWEEN 4000 AND 8000;

SELECT DISTINCT subject_id
FROM teacher;

SELECT DISTINCT title
FROM teacher
WHERE gender='女';

SELECT DISTINCT job
FROM student
WHERE age BETWEEN 7 AND 10
  AND gender='男';

SELECT name,comm,title
FROM teacher
WHERE title IN('一级讲师','二级讲师');

SELECT name,salary,comm
FROM teacher
WHERE title NOT IN('老板','总监');

SELECT name,floor
FROM class
WHERE name IN('3年级2班','5年级3班');


SELECT name,age,gender,salary
FROM teacher
WHERE name LIKE '%晶%';

SELECT name,age,gender
FROM student
WHERE name LIKE '张%';

SELECT name,gender,age
FROM student
WHERE name LIKE '_平_';

SELECT name,age,gender
FROM teacher
WHERE name LIKE '%晶';

SELECT name,age,gender
FROM student
WHERE name LIKE '李%';

SELECT name,age,gender
FROM student
WHERE name LIKE '%江%';

SELECT name,age,gender
FROM student
WHERE name LIKE '%郭';

SELECT name,age,gender,job
FROM student
WHERE age BETWEEN 9 AND 12
   AND job LIKE '%课代表%';

SELECT name,age,gender
FROM student
WHERE name LIKE '_苗%';

SELECT name,age,gender,job
FROM student
WHERE name LIKE '邱%'
AND job LIKE '%课代表%';

SELECT name,age,gender,job
FROM student
WHERE job LIKE '%课代表%';

SELECT id,name,floor
FROM class
WHERE name LIKE '%2班%';

SELECT name,age,gender,comm
FROM teacher
WHERE comm=NULL ;   #永远不成立

SELECT name,age,gender,comm
FROM teacher
WHERE comm IS NULL ;

SELECT name,age,gender,comm
FROM teacher
WHERE comm<>NULL;   #永远不成立

SELECT name,age,gender,comm
FROM teacher
WHERE comm IS NOT NULL ;

SELECT name,salary
FROM teacher
ORDER BY salary;

SELECT name,salary
FROM teacher
ORDER BY salary DESC;

SELECT name,salary,comm
FROM teacher
ORDER BY comm;

SELECT name,age,birth
FROM student
ORDER BY birth;

SELECT name,age,birth
FROM student
WHERE age BETWEEN 7 AND 10
ORDER BY birth ;

SELECT name,comm,salary
FROM teacher
ORDER BY comm ASC,salary DESC;
#LIMIT M,N
#M跳过几条
#N M+1条开始看N条
SELECT name,salary
FROM teacher
ORDER BY salary DESC
LIMIT 0,5;

SELECT name,comm
FROM teacher
ORDER BY comm DESC
LIMIT 12,3;

SELECT name,age,birth
FROM student
ORDER BY birth
LIMIT 10,5;

SELECT name,salary,salary*12
FROM teacher;

SELECT name,salary,comm,salary+comm
FROM teacher;

SELECT name,salary,comm,salary+IFNULL(comm,0)
FROM teacher;

SELECT name,IFNULL(comm,0),IFNULL(comm*12,0)
FROM teacher;

SELECT name,salary
FROM teacher
WHERE salary*12>60000
ORDER BY salary DESC;

SELECT name,comm
FROM teacher
WHERE comm<3000;

SELECT name,comm
FROM teacher
WHERE IFNULL(comm,0)<3000;

#列别名,下列句子等价
SELECT name,salary,salary*12 annusal
FROM teacher;
SELECT name,salary,salary*12 AS annusal
FROM teacher;
SELECT name,salary,salary*12 AS 'annusal'
FROM teacher;
SELECT name,salary,salary*12 AS "annusal"
FROM teacher;
SELECT name,salary,salary*12 'annusal'
FROM teacher;
SELECT name,salary,salary*12 "annusal"
FROM teacher;

SELECT name,salary,salary*12 'ann usal'
FROM teacher;

SELECT name,salary,salary*12 'from'
FROM teacher;

SELECT name,age,birth
FROM student
WHERE age=10
ORDER BY birth;

SELECT name,age
FROM student
WHERE age=8 AND name LIKE '%苗%';

SELECT name,age,subject_id,salary
FROM teacher
WHERE subject_id IN (1,2) AND salary>6000;

SELECT name,age,job
FROM student
WHERE age>10 AND job IN ('语文课代表','数学课代表');

SELECT name,subject_id,salary
FROM teacher
WHERE NOT subject_id=1
ORDER BY salary DESC;

SELECT name,comm
FROM teacher
WHERE IFNULL(comm,0)=0;

SELECT name,comm
FROM teacher
ORDER BY comm DESC;

SELECT DISTINCT subject_id
FROM teacher
WHERE salary>8000;

SELECT id,name,age,birth
FROM student
ORDER BY birth DESC,id
LIMIT 5,5;

SELECT AVG(salary)
FROM teacher;

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM teacher;

SELECT AVG(salary)
FROM teacher
WHERE subject_id=1;

SELECT name
FROM teacher;

SELECT COUNT(name)
FROM teacher;

SELECT COUNT(comm)
FROM teacher;#20个老师17个奖金

SELECT AVG(comm)
FROM teacher;#3041.1765

SELECT SUM(comm)
FROM teacher;#51700

SELECT IFNULL(comm,0)
FROM teacher;
SELECT AVG(IFNULL(comm,0))
FROM teacher;
SELECT AVG(IFNULL(comm,0)) '平均奖金'
FROM teacher;

SELECT COUNT(comm)
FROM teacher;#17
SELECT COUNT(*)
FROM teacher;
SELECT COUNT(1)
FROM teacher;
SELECT COUNT(id)
FROM teacher;

SELECT AVG(IFNULL(comm,0)),SUM(comm)
FROM teacher;

SELECT COUNT(*)
FROM teacher
WHERE subject_id=2;

SELECT COUNT(*)
FROM student
WHERE class_id=1;

SELECT MIN(birth)
FROM student;

SELECT COUNT(*)
FROM student
WHERE age=11 AND job LIKE '%课代表%';

SELECT COUNT(*)
FROM student
WHERE name LIKE '张%';

SELECT MIN(salary)
FROM teacher
WHERE salary>5000;

SELECT COUNT(*)
FROM class
WHERE floor=4;

SELECT AVG(IFNULL(salary,0))
FROM teacher
WHERE title='总监';

SELECT AVG(salary),title
FROM teacher
GROUP BY title;

SELECT COUNT(*),class_id
FROM student
GROUP BY class_id;

SELECT  COUNT(*) '人数',
        MIN(birth) '最大生日',
        MAX(birth) '最小生日',
        job
FROM student
GROUP BY job;

SELECT COUNT(*),class_id,gender
FROM student
GROUP BY class_id,gender;

SELECT COUNT(*),class_id,job
FROM student
GROUP BY class_id,job;

SELECT AVG(salary) '平均工资',subject_id
FROM teacher
GROUP BY subject_id
ORDER BY '平均工资' DESC;

SELECT AVG(salary) '平均工资',
       subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id;

SELECT AVG(salary) '平均工资',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(salary)>6000;

SELECT AVG(salary) '平均工资',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary)>9000;

SELECT SUM(salary) '工资总和',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(comm)>4000;

SELECT AVG(salary)
FROM teacher
WHERE gender='男'
GROUP BY subject_id
HAVING MIN(salary)>4000;

SELECT COUNT(*),class_id
FROM student
WHERE class_id<6
GROUP BY class_id;

SELECT COUNT(*)
FROM class
WHERE floor=3;

SELECT AVG(salary)
FROM teacher
WHERE salary<8000;

SELECT MIN(birth) '年纪最大',
       class_id
FROM student
GROUP BY class_id
HAVING COUNT(*)>60;
6:查看班级人数超过60人的班级中年纪最大的同学生日是多少?
SELECT MIN(birth)
FROM student
WHERE class_id IN (
    SELECT class_id
    FROM student
    GROUP BY class_id
    HAVING COUNT(*)>60
);
SELECT COUNT(*),class_id
FROM student
GROUP BY class_id;#每个班有多少人

SELECT AVG(age)
FROM teacher
WHERE subject_id=1;

SELECT MIN(age),subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(age)>35;

SELECT AVG(salary),title
FROM teacher
GROUP BY title
HAVING COUNT(*)>4;


SELECT SUM(salary) '工资总和',
       subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(IFNULL(comm,0))>4000;

SELECT AVG(salary),subject_id
FROM teacher
WHERE gender='男'
GROUP BY subject_id
HAVING MIN(salary)>4000;

SELECT COUNT(*),class_id
FROM student
WHERE class_id<6
GROUP BY class_id;

SELECT COUNT(*)
FROM class
WHERE floor=3;

SELECT AVG(salary)
FROM teacher
WHERE salary<8000;

SELECT MIN(birth) '年纪最大',
       class_id
FROM student
GROUP BY class_id
HAVING COUNT(*)>60;

SELECT AVG(age)
FROM teacher
WHERE subject_id=1;

SELECT MIN(age),subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(age)>35;

SELECT AVG(salary),title
FROM teacher
GROUP BY title
HAVING COUNT(*)>4;

SELECT COUNT(*),class_id
FROM student
GROUP BY class_id;#每个班有多少人

#比王克晶工资高的老师都有谁？
SELECT salary
FROM teacher
WHERE name='王克晶';
SELECT name,salary
FROM teacher
WHERE salary>(SELECT salary
              FROM teacher
              WHERE name='王克晶');

SELECT AVG(salary)
FROM teacher;
SELECT salary
FROM teacher
WHERE salary>(SELECT AVG(salary)
              FROM teacher);

SELECT name
FROM student
WHERE class_id=(SELECT class_id
                FROM student
                WHERE name='李费水');

SELECT name
FROM class
WHERE floor=(SELECT floor
             FROM class
             WHERE name='3年级2班');

SELECT name,salary,comm
FROM teacher
WHERE salary=(SELECT MAX(salary)
              FROM teacher);
SELECT name,salary,comm
FROM teacher
GROUP BY salary DESC
LIMIT 0,1;

SELECT name
FROM student
WHERE class_id=(SELECT id
                FROM class
                WHERE name='3年级2班');

SELECT name
FROM teacher
WHERE subject_id=(SELECT id
                  FROM subject
                  WHERE name='语文');

SELECT name,class_id
FROM student
WHERE class_id IN (SELECT class_id
                   FROM student
                   WHERE name IN ('祝雷','李费水'));

SELECT name
FROM teacher
WHERE subject_id NOT IN (SELECT subject_id
                         FROM teacher
                         WHERE name IN ('范传奇','王克晶'));

SELECT name,salary
FROM teacher
WHERE salary>ALL(SELECT salary
                 FROM teacher
                 WHERE subject_id IN (2,4));

SELECT name,salary,subject_id
FROM teacher;
UPDATE teacher
SET salary=salary-500
WHERE subject_id=(SELECT subject_id
                  FROM teacher
                  WHERE name='范传奇');

UPDATE teacher
SET salary=(SELECT salary
            FROM teacher
            WHERE name='范传奇')
WHERE name='王克晶';
UPDATE teacher
SET salary=8000
WHERE name='王克晶';

BEGIN ;#开启事务
#删除与范传奇教同一科目的所有老师
#DELETE FROM teacher
#WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='范传奇');
SELECT * FROM teacher;
ROLLBACK ;#回滚,BEGIN到ROLLBACK之间的所有操作全部撤销
COMMIT ;#提交，BEGIN到COMMIT之间的所有操作全部执行

SELECT MAX(salary) max_sal,
       MIN(salary) min_sal,
       AVG(salary) avg_sal,
       SUM(salary) sum_sal,
       subject_id
FROM teacher
GROUP BY subject_id;

CREATE TABLE t_teacher_salary_info
AS
SELECT MAX(salary) max_sal,
       MIN(salary) min_sal,
       AVG(salary) avg_sal,
       SUM(salary) sum_sal,
       subject_id
FROM teacher
GROUP BY subject_id;
SHOW TABLES;
SELECT * FROM t_teacher_salary_info;
#查看负责课程编号1的老师最高工资是多少
SELECT max_sal
FROM t_teacher_salary_info
WHERE subject_id=1;

CREATE VIEW v_teacher_info
AS
SELECT id,name,title,gender,age,subject_id
FROM teacher;

SELECT  * FROM v_teacher_info;

SELECT teacher.name,subject.name
FROM teacher,subject
WHERE teacher.subject_id=subject.id;

SELECT class.name,teacher.name
FROM class,teacher
WHERE teacher.id=class.teacher_id;
SELECT name,id
FROM teacher;
SELECT name,teacher_id
FROM class;

SELECT c.name,t.name
FROM class c,teacher t
WHERE t.id=c.teacher_id;

SELECT s.name,s.age,c.name,c.floor
FROM class c,student s
WHERE c.id=s.class_id;

SELECT s.name,s.gender,s.age,l.name
FROM student s,location l
WHERE l.id=s.location_id;
SELECT name,location_id
FROM student
WHERE location_id=0;
SELECT name,id
FROM location;

SELECT c.name,t.name
FROM class c,teacher t
WHERE t.id=c.teacher_id
AND t.name='王克晶';

SELECT l.name,s.name,s.age,s.gender
FROM location l,student s
WHERE l.id=s.location_id
AND l.name='南京';

SELECT c.name,c.floor,t.name,t.salary
FROM teacher t,class c
WHERE t.id=c.teacher_id
AND c.name LIKE '3年级%';

SELECT s.name,s.age,s.gender,s.job,c.name,c.floor
FROM student s,class c
WHERE s.class_id=c.id
AND c.name LIKE '5年级%'
AND s.job='中队长';
SELECT *
FROM class;

SELECT s.name,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.name='范传奇';

SELECT s.name,l.name,c.name
FROM student s,location l,class c
WHERE s.location_id=l.id AND s.class_id=c.id
AND c.name='1年级1班';

SELECT l.name,s.name
FROM location l,student s
WHERE l.id=s.location_id
AND l.name='北京';

SELECT t.name,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
AND s.name='英语';

SELECT s.name,c.name,t.name
FROM student s,teacher t,class c
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.name='刘苍松';

SELECT t.name,s.name,c.name
FROM teacher t,subject s,class c
WHERE t.subject_id=s.id AND c.teacher_id=t.id
AND s.name='语文';

SELECT DISTINCT l.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND t.name='王克晶';

SELECT t.name,s.name,c.name
FROM teacher t,class c,subject s
WHERE t.id=c.teacher_id AND t.subject_id=s.id
AND c.name LIKE '3年级%';

SELECT s.name,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.salary>10000
AND s.job='大队长';
SELECT name,salary
FROM teacher;

SELECT su.name,t.name,s.name,c.name
FROM teacher t,class c,student s,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id AND t.subject_id=su.id
AND s.name='李费水';

SELECT t.name,s.name,c.name
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND c.floor=4
AND s.job IN ('大队长','中队长');

SELECT s.name,c.name,t.name,s.birth
FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND s.birth=(SELECT MAX(birth)
             FROM student);

SELECT su.name,t.name,c.name,s.name,l.name
FROM student s,class c,teacher t,subject su,location l
WHERE s.class_id=c.id AND c.teacher_id=t.id
AND t.subject_id=su.id AND s.location_id=l.id
AND su.name='语文';

SELECT s.name,c.name,t.name,l.name
FROM student s,class c,teacher t,location l
WHERE s.class_id=c.id AND c.teacher_id=t.id AND s.location_id=l.id
AND s.birth=(SELECT MAX(birth)
             FROM student);

SELECT s.name,c.name,t.name,l.name
FROM student s,class c,teacher t,location l
WHERE s.class_id=c.id AND c.teacher_id=t.id AND s.location_id=l.id
AND t.salary=(SELECT MAX(salary)
              FROM teacher)
AND l.name='北京';

SELECT t.name,s.name,c.name
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND t.name='范传奇';
SELECT COUNT(*)
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id AND c.id=s.class_id
  AND t.name='范传奇';

SELECT AVG(t.salary) '平均工资'
FROM teacher t,subject s
WHERE t.subject_id=s.id
AND s.name='语文';

SELECT t.name,t.salary,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id;
SELECT AVG(t.salary),s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
GROUP BY s.name;

SELECT AVG(t.salary),s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
GROUP BY s.name
HAVING AVG(t.salary)>6000;

SELECT l.name,t.name,s.name,c.name
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND t.salary=(SELECT MAX(salary) FROM teacher);
SELECT l.name,COUNT(*) '人数'
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY l.name,t.name,c.name
HAVING COUNT(*)>3
ORDER BY 人数 DESC;

1:先将待统计的数据查询出来-工资最高的老师班里的学生和城市信息
1.1:未知条件?最高工资是多少
SELECT MAX(salary) FROM teacher
    1.2:数据都来自哪些表?
    teacher t,class c,student s,location l
1.3:连接条件?
    t.id=c.teacher_id , c.id=s.class_id , l.id=s.location_id
1.4:过滤条件?
    t.salary=最高工资(1.1子查询)
SELECT t.name,c.name,s.name,l.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND l.id=s.location_id
  AND t.salary=(SELECT MAX(salary) FROM teacher);

2:分组
每个城市的学生各多少人
GROUP BY l.name

3:过滤分组?(仅查看超过三个人的城市)该分组记录数超过3条
HAVING COUNT(*)>3

4:添加聚合函数进行分组统计
SELECT COUNT(*),l.name,t.name,c.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND l.id=s.location_id
  AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY l.name,t.name,c.name
HAVING COUNT(*)>3;

5:排序,人数从多到少
SELECT COUNT(*) '人数',l.name,t.name,c.name                              #5
FROM teacher t,class c,student s,location l                             #1
WHERE t.id=c.teacher_id AND c.id=s.class_id AND l.id=s.location_id      #2
  AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY l.name,t.name,c.name                                           #3
HAVING COUNT(*)>3                                                       #4
ORDER BY 人数 DESC;














SELECT COUNT(*)
FROM teacher t,student s,class c
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND t.name=(
    SELECT name
    FROM teacher
    HAVING MAX(salary)
    );
SELECT COUNT(*)
FROM teacher t,student s,class c
WHERE t.id=c.teacher_id AND c.id=s.class_id
  AND t.salary=(
    SELECT MAX(salary)
    FROM teacher
);#对
SELECT *
FROM teacher;

1:教语文的老师所带班级各多少学生?
SELECT t.name,COUNT(*)
FROM teacher t,student s,class c,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id AND t.subject_id=su.id
AND su.name='语文'
GROUP BY t.name;

2:每门课的老师所带班级各多少学生?
SELECT su.name,COUNT(*)
FROM teacher t,student s,class c,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id AND t.subject_id=su.id
GROUP BY su.name;

3:来自上海的学生的班主任都有谁?
SELECT DISTINCT t.name
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND l.name='上海';

4:来自南京的学生共多少人
SELECT COUNT(*)
FROM student s,location l
WHERE l.id=s.location_id
AND l.name='南京';

5:来自武汉的男同学和女同学分别多少人?
SELECT COUNT(*),s.gender
FROM student s,location l
WHERE l.id=s.location_id
AND l.name='武汉'
GROUP BY s.gender;

6:每个城市的学生各多少人
SELECT COUNT(*),l.name
FROM student s,location l
WHERE l.id=s.location_id
GROUP BY l.name;

7:高于平均工资的老师所带的班级分别多少学生?
SELECT t.name,COUNT(*),c.name
FROM teacher t,student s,class c
WHERE t.id=c.teacher_id AND c.id=s.class_id
AND t.salary>(
    SELECT AVG(salary)
    FROM teacher
)
GROUP BY t.name;

8:每个老师班里各多少来自郑州的学生?
SELECT t.name,COUNT(*)
FROM teacher t,student s,class c,location l
WHERE t.id=c.teacher_id AND c.id=s.class_id AND s.location_id=l.id
AND l.name='郑州'
GROUP BY t.name;

SELECT s.name,su.name,tss.score
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND su.id=tss.subject_id
  AND su.name='语文';

SELECT s.name,su.name,tss.score
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND su.id=tss.subject_id
  AND s.name='李费水';

SELECT *
FROM t_stu_subject_score;
DESC t_stu_subject_score;

SELECT s.name,c.name,tss.score,su.name
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND s.class_id=c.id AND tss.subject_id=su.id
AND c.name='1年级1班' AND su.name='语文';

SELECT COUNT(*),c.name,AVG(tss.score),su.name
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND s.class_id=c.id AND tss.subject_id=su.id
AND c.name='1年级1班' AND su.name='数学';

SELECT COUNT(*),AVG(tss.score),su.name
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND s.class_id=c.id AND tss.subject_id=su.id
AND c.name LIKE '6年级%' AND su.name='英语';

SELECT AVG(tss.score)
FROM student s,class c,t_stu_subject_score tss,subject su,teacher t
WHERE t.id=c.teacher_id AND c.id=s.class_id
    AND s.id=tss.stu_id AND tss.subject_id=su.id
AND t.name='刘苍松' AND su.name='英语';

SELECT AVG(tss.score),MAX(tss.score),MIN(tss.score),su.name
FROM teacher t,class c,student s,t_stu_subject_score tss,subject su
WHERE t.id=c.teacher_id AND c.id=s.class_id
  AND s.id=tss.stu_id AND su.id=tss.subject_id
  AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY su.name;

SELECT AVG(tss.score),s.name
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id AND su.id=tss.subject_id
  AND s.job='大队长'
GROUP BY s.name;

SELECT s.name,c.name,s.job
FROM class c,student s
WHERE c.id=s.class_id
AND c.name='1年级1班';
#上下两个表一样
SELECT s.name,c.name,s.job
FROM class c
JOIN student s ON c.id=s.class_id
WHERE c.name='1年级1班';

SELECT s.name,c.name,t.name
FROM teacher t
JOIN class c ON t.id=c.teacher_id
JOIN student s ON c.id=s.class_id
WHERE t.name='王克晶' AND s.gender='女';

SELECT t.name,su.name
FROM teacher t
JOIN subject su ON t.subject_id=su.id
WHERE su.name='英语';

SELECT c.name,t.name
FROM class c
JOIN teacher t ON t.id=c.teacher_id;

SELECT t.name,c.name,s.name,l.name,tss.score,su.name
FROM teacher t
JOIN class c ON t.id=c.teacher_id
JOIN student s ON c.id=s.class_id
JOIN location l ON l.id=s.location_id
JOIN t_stu_subject_score tss ON tss.stu_id=s.id
JOIN subject su ON su.id=tss.subject_id
WHERE t.name='刘苍松' AND l.name='南京' AND su.name='语文';

SELECT t.name,c.name,s.name,l.name,AVG(tss.score),su.name
FROM teacher t
         JOIN class c ON t.id=c.teacher_id
         JOIN student s ON c.id=s.class_id
         JOIN location l ON l.id=s.location_id
         JOIN t_stu_subject_score tss ON tss.stu_id=s.id
         JOIN subject su ON su.id=tss.subject_id
WHERE t.name='刘苍松' AND su.name='语文'
GROUP BY c.name,l.name;

SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT JOIN teacher t ON t.id=c.teacher_id;
#上下两表一样
SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT OUTER JOIN teacher t ON t.id=c.teacher_id;

SELECT c.name,c.floor,t.name,t.salary
FROM class c
RIGHT JOIN teacher t ON t.id=c.teacher_id;


SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT JOIN teacher t ON t.id=c.teacher_id
UNION
SELECT c.name,c.floor,t.name,t.salary
FROM class c
RIGHT JOIN teacher t ON t.id=c.teacher_id;

SELECT COUNT(*) '人数','姓张' AS '姓氏'
FROM student
WHERE  name LIKE '张%'
UNION
SELECT COUNT(*) '人数','姓李' AS '姓氏'
FROM student
WHERE  name LIKE '李%'
UNION
SELECT COUNT(*) '人数','姓王' AS '姓氏'
FROM student
WHERE  name LIKE '王%';

SELECT *
FROM teacher;
SELECT t.name,m.name
FROM teacher t
JOIN teacher m ON t.manager=m.id;

SELECT s.name '学生',l.name '班长'
FROM student s
JOIN student l ON s.team_leader=l.id
WHERE s.name='祝雷';

SELECT s.name '学生',l.name '班长',s.birth
FROM student s
JOIN student l ON s.team_leader=l.id
WHERE s.birth=(SELECT MIN(birth)FROM student);

SELECT t.name,m.name,c.name
FROM class c
JOIN teacher t ON t.id=c.teacher_id
JOIN teacher m ON t.manager=m.id
WHERE c.name='3年级2班';

SELECT s.name'学生',c.name'班级',t.name'老师',m.name'上司'
FROM student s
JOIN class c ON s.class_id=c.id
JOIN teacher t ON t.id=c.teacher_id
JOIN teacher m ON t.manager=m.id
WHERE s.birth=(SELECT MIN(birth)FROM student);

SHOW DATABASES ;
USE tedu;
SHOW TABLES ;
DESC userinfo;
SELECT *
FROM userinfo;


2024.10.10作业
创建库 egdb1 并指定字符集为UTF8
CREATE DATABASE egdb1 CHARSET=UTF8;
查看所有的库
SHOW DATABASES ;
切换到库 egdb1
USE egdb1;
在该库中创建数据表 books 并指定字符集为UTF8，字段类型和约束要求如下：
书籍编号id：整型，设置为主键并设置自增长属性
书籍名称bname：变长，宽度为50，不能为NULL
书籍作者author：变长，宽度为30，不能为NULL，设置默认值为 '佚名'
书籍出版社press：变长，宽度为128
书籍价格price：浮点型，总位数为7，小数位为2，不能为NULL，设置默认值为 0.00
书籍描述comment：text类型，不用指定宽度
CREATE TABLE books(
    id INT PRIMARY KEY AUTO_INCREMENT,
    bname VARCHAR(50) NOT NULL,
    author VARCHAR(30) NOT NULL DEFAULT '佚名',
    press VARCHAR(128),
    price DOUBLE(7,2) NOT NULL DEFAULT 0.00,
    comment TEXT
);
查看表结构
DESC books;
SHOW TABLES ;
在表中任意插入1条表记录；
INSERT INTO books (bname)
    VALUES ('Java从入门到精通');
查询所有表记录；
SELECT * FROM books;
在表中批量插入表记录
INSERT INTO books(bname,author,press,price,comment)
VALUES
    ('边城','沈从文','机械工业出版社',36,'小城故事多'),
    ('骆驼祥子','老舍','机械工业出版社',43,'你是祥子么'),
    ('茶馆','老舍','中国文学出版社',55,'老北京'),
    ('呐喊','鲁迅','人民教育出版社',71,'最后的声音'),
    ('朝花夕拾','鲁迅','中国文学出版社',53,'好时光'),
    ('围城','钱钟书','中国文学出版社',44,'你心中的围城是什么');

INSERT INTO books(bname,author,press,price)
VALUES
    ('林家铺子','茅盾','机械工业出版社',51),
    ('子夜','茅盾','人民教育出版社',47);

SQL语句练习

查找人民教育出版社出版的图书
SELECT *
FROM books
WHERE press='人民教育出版社';
查找老舍写的，中国文学出版社出版的图书
SELECT *
FROM books
WHERE author='老舍' AND press='中国文学出版社';
查找价格超过60元[不包括60元]的图书，只看书名和价格
SELECT bname,price
FROM books
WHERE price>60;
将呐喊的价格修改为45元
UPDATE books
SET price=45
WHERE bname='呐喊';
增加一个字段出版时间，类型为 date ，放在价格字段price后面
ALTER TABLE books
ADD date DATE AFTER price;#date可以保存年月日
修改所有老舍的作品出版时间为 2018-10-01
UPDATE books
SET date='2018-10-01'
WHERE author='老舍';
所有鲁迅的图书价格增加5元
UPDATE books
SET price=price+5
WHERE author='鲁迅';
删除所有价格超过70元[包含70元]或者不到40元[不包含40元]的图书
DELETE FROM books
WHERE price>=70 OR price<40;
SELECT * FROM books;
 */